package com.openatc.agent.service;

import com.google.gson.*;
import com.openatc.agent.model.FaultRange;
import com.openatc.agent.model.WebOperationRecordRange;
import com.openatc.comm.data.MessageData;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

/**
 * @author ：zhangwenchao
 * @date ：Created in 2021/10/18 17:44
 * @description：历史数据数据库操作类
 * @modified By：
 * @version: $
 */

@Repository
public class HistoryDataDao {

    private static Logger logger = Logger.getLogger(HistoryDataDao.class.toString());

    @Autowired
    public JdbcTemplate jdbcTemplate;

    private static Map<Integer,String> tagMap = null;

    // 保存历史流量数据
    public int SaveFlowData(MessageData msg){
        String sql = String.format("INSERT INTO flow (agentid,time,data) VALUES ('%s','%s','%s')", msg.getAgentid(),msg.getCreatetime(),msg.getData().toString());
        int rows = jdbcTemplate.update(sql);
        return rows;
    }

//    // 保存历史方案数据
//    public int SavePatternData(MessageData msg){
//        JsonObject msgdata = null;
//        int fiveminIndex;
//        int fifteenminIndex;
//        int sixtyminIndex;
//        int weekIndex;
//        int cycle;
//        try{
//            boolean isEffective = judgeMsg(msg);
//            if (!isEffective) return 0;
//
//            msgdata = msg.getData().getAsJsonObject();
//
//            msgdata.remove("overlap");
//
//            JsonArray phases = msgdata.get("phase").getAsJsonArray();
//            for (JsonElement phase : phases) {
//                JsonObject phosedata = phase.getAsJsonObject();
//                phosedata.remove("conphase");
//                phosedata.remove("type");
//                phosedata.remove("countdown");
//                phosedata.remove("pedtype");
//                phosedata.remove("pedcountdown");
//                phosedata.remove("close");
//            }
//            fiveminIndex =  DateUtil.getTimeCount(DateUtil.dateNormalStrToTimestamp(msg.getCreatetime()),300000); // 当前时间是第几个5分钟
//            fifteenminIndex = DateUtil.getTimeCount(DateUtil.dateNormalStrToTimestamp(msg.getCreatetime()),300000 * 3);; // 当前时间是第几个15分钟
//            sixtyminIndex = DateUtil.getTimeCount(DateUtil.dateNormalStrToTimestamp(msg.getCreatetime()),300000 * 12);; // 当前时间是第几个60分钟
//            weekIndex = DateUtil.getDayofweek();
//            cycle = msgdata.get("cycle").getAsInt();
//
//        }catch (Exception e){
//            logger.warning("Save Pattern Data error!:" + e.getMessage());
//            return 0;
//        }
//
//        String sql = String.format("INSERT INTO pattern (agentid,time,data,cycle,fivemintag,fifteenmintag,sixtymintag,week) VALUES " +
//                        "('%s','%s','%s','%s','%s','%s','%s','%s')",
//                msg.getAgentid(),msg.getCreatetime(),msgdata.toString(),cycle,fiveminIndex,fifteenminIndex,sixtyminIndex,weekIndex);
//        int rows = jdbcTemplate.update(sql);
//        return rows;
//    }

//    /**
//     * 判断当前方案数据是否有效
//     * @param msg
//     * @return
//     */
//    private boolean judgeMsg(MessageData msg) {
//        JsonElement data = msg.getData();
//        if (data == null) return false;
//
//        if (msg.getCreatetime() == null) return false;
//
//        Date date = DateUtil.stringToDate(msg.getCreatetime());
//        long createTime = date.getTime();
//        long now = System.currentTimeMillis();
//        if (now - createTime > 5000) return false;
//
//        JsonObject msgdata = data.getAsJsonObject();
//        // 没有相位数据
//        JsonElement phaseElement = msgdata.get("phase");
//        if (phaseElement == null) return false;
//
//        return true;
//    }


    // 查询历史流量数据
    public List<HistoryData> GetFlowData(String agentId, String beginTime, String endTime){
        String sql = String.format("SELECT time,data FROM flow where agentid = '%s' and time between '%s' and '%s' order by time asc ", agentId,beginTime,endTime);
        List<Map<String, Object>> lvRet =  jdbcTemplate.queryForList(sql);
        return convertHistoryData(lvRet);
    }

    // 查询历史方案数据
    public List<HistoryData> GetPatternData(String agentId, String beginTime, String endTime) {
        String sql = String.format("SELECT time,data FROM pattern where agentid = '%s' and time between '%s' and '%s' order by time asc ", agentId, beginTime, endTime);
        List<Map<String, Object>> lvRet = jdbcTemplate.queryForList(sql);
        return convertHistoryData(lvRet);
    }

    public List<HistoryData> GetPatternRealtimeData(String agentId, String beginTime, String endTime) {
        String sql = String.format("SELECT time,data FROM pattern_realtime where agentid = '%s' and time between '%s' and '%s' order by time asc ", agentId, beginTime, endTime);
        List<Map<String, Object>> lvRet = jdbcTemplate.queryForList(sql);
        return convertHistoryData(lvRet);
    }

    // 将查询结果转换为历史数据对象
    private List<HistoryData> convertHistoryData(List<Map<String, Object>> lvRet){
        List<HistoryData> hdList = new ArrayList<>();
        for (Map map : lvRet) {
            HistoryData hd = new HistoryData();
            hd.time = map.get("time").toString();
            hd.data = new JsonParser().parse(map.get("data").toString()).getAsJsonObject();
            hdList.add(hd);
        }
        return hdList;
    }

    public JsonArray getIntOperationCount(String agentid, String startTime, String endTime, int type, String intervalType) {
        Gson gson = new GsonBuilder().setDateFormat("yyyy-MM-dd HH:mm:ss").create();
        String sql = String.format("select count(sub_info_type) as count,DATE_TRUNC('%s', opertime::timestamp) as time " +
                "from operation_record where agentid='%s' and sub_info_type=%s and opertime >= '%s' and opertime <= '%s' " +
                "GROUP BY time ORDER BY time",
                intervalType,agentid,type,startTime,endTime);
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        JsonElement jsonElement = gson.toJsonTree(maps);
        return jsonElement.getAsJsonArray();
    }

    /**
     * 获取设备总数
     * @param countsql
     * @return
     */
    public int getDevCount(String countsql) {
        return jdbcTemplate.queryForObject(countsql, int.class);
    }

    /**
     * 获取cycle数据
     * @param agentid 路口id
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param interval 间隔
     * @param weeks 周
     * @return
     */
    public JsonArray getCycleHisData(String agentid, String startTime, String endTime, int interval, List<Integer> weeks) {
        Gson gson = new GsonBuilder().create();
        String intervalTag = getIntervalTag(interval);

        String baseSql = String.format("select %s as timeindex,avg(cycle) as cycle from pattern where agentid='%s' and %s NOTNULL and time >= '%s' and time <= '%s'",
                intervalTag,agentid,intervalTag,startTime,endTime);

        String whereCondition = "";
        if (!CollectionUtils.isEmpty(weeks)) {
            whereCondition = String.format(" and week in (%s) ",getBuffer(weeks));
        }

        String groupBy = String.format("group by %s order by %s",intervalTag,intervalTag);

        String sql = "";
        if (StringUtils.hasLength(whereCondition)) {
            sql = baseSql.concat(whereCondition).concat(groupBy);
        } else {
            sql = baseSql.concat(groupBy);
        }
        List<Map<String, Object>> cycles = jdbcTemplate.queryForList(sql);
        JsonArray cycleData = new JsonArray();
        if (!CollectionUtils.isEmpty(cycles)) {
            for (Map<String,Object> cycle : cycles) {
                cycleData.add(gson.toJsonTree(cycle));
            }
        }

        return cycleData;
    }


    // 拼接sql中in后面的字符
    private StringBuffer getBuffer(List<Integer> strings) {
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < strings.size(); i++){
            if (i != strings.size() - 1){
                sb.append("'" + strings.get(i) + "',");
            }else {
                sb.append("'"+ strings.get(i) + "'");
            }
        }
        return sb;
    }

    private String getIntervalTag(int interval) {
        if (tagMap == null) {
            tagMap = new HashMap<>();
            tagMap.put(5,"fivemintag");
            tagMap.put(15,"fifteenmintag");
            tagMap.put(60,"sixtymintag");
        }
        return tagMap.get(interval);
    }

    public JsonArray getPlatFormOperation(WebOperationRecordRange requestData) {
        Gson gson = new GsonBuilder().setDateFormat("yyyy-MM-dd HH:mm:ss").create();
        String sql = String.format("select count(*) as count,DATE_TRUNC('%s', opertime::timestamp) as time " +
                        "from web_operation_record where operator='%s' and model='%s' and infotype='%s' and opertime >= '%s' and opertime <= '%s' " +
                        "GROUP BY time ORDER BY time",
                requestData.getIntervalType(),requestData.getOperator(),requestData.getModel(),requestData.getInfotype(),requestData.getBeginTime(),requestData.getEndTime());
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        JsonElement jsonElement = gson.toJsonTree(maps);
        return jsonElement.getAsJsonArray();
    }

    public JsonArray getFaultStatistics(FaultRange requestData) {
        Gson gson = new GsonBuilder().setDateFormat("yyyy-MM-dd HH:mm:ss").create();

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        long bTime;
        long eTime;
        try {
            bTime = format.parse(requestData.getBeginTime()).getTime() / 1000;
            eTime = format.parse(requestData.getEndTime()).getTime() / 1000;
        } catch (ParseException e) {
            bTime = 0;
            eTime = System.currentTimeMillis();
        }

        String sql = String.format("select count(*) as count,DATE_TRUNC('%s', to_timestamp(m_un_fault_occur_time)) as time " +
                " from fault where agentid='%s' and m_by_fault_board_type=%s and m_w_fault_type=%s and m_w_sub_fault_type=%s and m_by_fault_level=%s " +
                " and m_un_fault_occur_time between %s and %s " +
                " GROUP BY time ORDER BY time",
                requestData.getIntervalType(),requestData.getAgentId(),requestData.getM_byFaultBoardType(),requestData.getM_wFaultType(),
                requestData.getM_wSubFaultType(),requestData.getM_byFaultLevel(),bTime,eTime);

        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        JsonElement jsonElement = gson.toJsonTree(maps);
        return jsonElement.getAsJsonArray();
    }

    class HistoryData {
        public String time;
        public JsonObject data;
    }
}
